<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
class collegian_model extends CI_Model {

    public function __construct() {
        error_reporting(0);
        parent::__construct();
    }
    
    public function update_collegian($input){
        $sql = "UPDATE  collegian SET 
                                   collegian_name='$input[txt_name]' ,
                                   collegian_code='$input[txt_code]' ,
                                   collegian_gender='$input[gender]' ,
                                   collegian_date='$input[year]-$input[month]-$input[day]' ,
                                   ref_major_id='$input[group_type]' ,
                                   collegian_phone='$input[phone_number]' ,
                                   collegian_email='$input[email]' 
                     WHERE  collegian_id='$input[coll_id]]'   ";
        $this->db->query($sql);
    }
    
    public function get_collegian_by_id( $id ){
        $id = mysql_escape_string($id);
        $this->db->select("*")->from("collegian");
        $this->db->join("majors","majors.major_id = collegian.ref_major_id");
        $this->db->where("collegian.collegian_id  =", $id);
        $this->db->limit(1);
        return $this->db->get()->row();
    }
    
    public function search_collegian($keyword,$group_type){
        $this->db->select(" SQL_CALC_FOUND_ROWS  *",false)->from("collegian");
        $this->db->join("majors","majors.major_id = collegian.ref_major_id");
        $this->db->where("( concat( collegian.collegian_name , ' ' , collegian.collegian_code  )   LIKE   '%$keyword%'  )",null,false);
        $this->db->limit(2,$_POST["page-limit"]);
       
        if(trim($group_type) != "*"){
            $this->db->where("collegian.ref_major_id  =",$group_type);
        }
        $this->db->order_by("major_title,collegian_code","ASC")->limit(15,$_POST["page-limit"]);
        return $this->db->get();
    }
    
    public function get_majors($id=null){
        $this->db->select("*")->from("majors")->order_by("major_id","ASC");
        if($id != null){
            $this->db->where("major_id =",$id);
        }
        return $this->db->get();
    }
    
    public function get_collegian_by_major_id($major_id){
        $this->db->select("*")->from("collegian")->where("ref_major_id",$major_id);
        return $this->db->get();
    }
    
   /* เลือกดูข้อมูลนักศึกษาเข้าร่วมกิจกรรม $status = 1 คือ ลงทะเบียน 2 คือ เข้าร่วม และ 3 คือ ไม่เข้าร่วม*/
    public function get_collegian_signin_activity($activity_id , $status = 1){

      
        switch ($status){
            case 1:  //ลงทะเบียน
                    $this->db->select("*,MID(collegians_of_activity.code_active,1,10) as  codestring ",false)->from("activity_events")
                            ->join("activity_groups_relation","activity_groups_relation.ref_act_id = activity_events.act_id","inner")
                            ->join("group_major_relation","group_major_relation.ref_activity_groups_relation_id = activity_groups_relation.activity_groups_relation_id","inner")
                            ->join("collegians_of_activity" , "collegians_of_activity.ref_group_major_relation_id = group_major_relation.group_major_relation_id","inner")
                            ->join("majors" , "majors.major_id = group_major_relation.ref_major_id","inner")
                            ->join("collegian" , "collegian.collegian_id = collegians_of_activity.ref_collegian_id","inner")
                            ->where("activity_events.act_id", $activity_id)
                            ->order_by("group_major_relation.ref_major_id","DESC");
                $this->db->where("collegians_of_activity.is_active","Y");
             
                //echo $this->db->_compile_select();
                return $this->db->get();
                break;
            case 2:  //เข้าร่วม
                    $this->db->select("*")->from("activity_events")
                            ->join("activity_groups_relation","activity_groups_relation.ref_act_id = activity_events.act_id","inner")
                            ->join("group_major_relation","group_major_relation.ref_activity_groups_relation_id = activity_groups_relation.activity_groups_relation_id","inner")
                            ->join("collegians_of_activity" , "collegians_of_activity.ref_group_major_relation_id = group_major_relation.group_major_relation_id","inner")
                            ->join("majors" , "majors.major_id = group_major_relation.ref_major_id","inner")
                            ->join("collegian" , "collegian.collegian_id = collegians_of_activity.ref_collegian_id","inner")
                            ->where("activity_events.act_id", $activity_id)
                            ->order_by("group_major_relation.ref_major_id","DESC");
                $this->db->where("collegians_of_activity.is_active","Y");
                $this->db->where("collegians_of_activity.allow_activity","Y");
               return $this->db->get();
                break;
            case 3:
                    $this->db->select("*")->from("activity_events")
                            ->join("activity_groups_relation","activity_groups_relation.ref_act_id = activity_events.act_id","inner")
                            ->join("group_major_relation","group_major_relation.ref_activity_groups_relation_id = activity_groups_relation.activity_groups_relation_id","inner")
                            ->join("collegians_of_activity" , "collegians_of_activity.ref_group_major_relation_id = group_major_relation.group_major_relation_id","inner")
                            ->join("majors" , "majors.major_id = group_major_relation.ref_major_id","inner")
                            ->join("collegian" , "collegian.collegian_id = collegians_of_activity.ref_collegian_id","inner")
                            ->where("activity_events.act_id", $activity_id)
                            ->order_by("group_major_relation.ref_major_id","DESC");
                $this->db->where("collegians_of_activity.is_active","Y");
                $this->db->where("collegians_of_activity.allow_activity","N");
               return $this->db->get();
               
                $sql = "
                      SELECT  *  FROM collegian INNER JOIN group_major_relation ON collegian.ref_major_id = group_major_relation.ref_major_id   
                      WHERE  collegian.collegian_id  NOT  IN (
                          SELECT  c.collegian_id  FROM collegian AS c INNER JOIN group_major_relation ON c.ref_major_id = group_major_relation.ref_major_id   
                          INNER  JOIN  activity_groups_relation ON activity_groups_relation.activity_groups_relation_id = group_major_relation.ref_activity_groups_relation_id  
                          INNER  JOIN  collegians_of_activity  ON  collegians_of_activity.ref_group_major_relation_id = group_major_relation.group_major_relation_id  
                          WHERE  activity_groups_relation.ref_act_id = '{$activity_id}'  ORDER  BY  collegian.collegian_code,collegian.ref_major_id  ASC
                      )
                            
                            
                    ";
                //return $this->db->query($sql);
        }
        
      
    }

    
}
?>
